package co.gov.medellin.ssti.sub.service;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.Collection;
import java.util.List;

import javax.naming.NamingException;

import co.gov.medellin.ssti.sub.common.ConexionFactory;
import co.gov.medellin.ssti.sub.model.dao.DAOFactory;
import co.gov.medellin.ssti.sub.model.dao.IProgramaDAO;
import co.gov.medellin.ssti.sub.model.dao.exception.DAOException;
import co.gov.medellin.ssti.sub.model.vo.ProgramaVO;

public class ProgramaService {

	/**
	 * 
	 * @return
	 * @throws SQLException
	 */
	public List<ProgramaVO> loadAllProgramas() throws DAOException {
		IProgramaDAO programaDAO = null;
		try {
			Connection con = ConexionFactory.getInstance().getConnection();
			programaDAO = DAOFactory.getDAOFactory(DAOFactory.ORACLE)
					.getProgramaDAO(con);
			return programaDAO.loadAllProgramas();
		} catch (NamingException e) {
			throw new DAOException(e);
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			if (programaDAO != null) {
				if (!programaDAO.isConnClosed()) {
					programaDAO.close();
				}
			}
		}
	}

	/**
	 * 
	 * @param secretaryCode
	 * @return
	 * @throws DAOException
	 */
	public List<ProgramaVO> loadAllProgramasByArea(String secretaryCode)
			throws DAOException {
		IProgramaDAO programaDAO = null;
		try {
			Connection con = ConexionFactory.getInstance().getConnection();
			programaDAO = DAOFactory.getDAOFactory(DAOFactory.ORACLE)
					.getProgramaDAO(con);
			return programaDAO.loadAllProgramasByArea(secretaryCode);
		} catch (NamingException e) {
			throw new DAOException(e);
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			if (programaDAO != null) {
				if (!programaDAO.isConnClosed()) {
					programaDAO.close();
				}
			}
		}
	}

	/**
	 * 
	 * @param codeUser
	 * @param userGroupName
	 * @param codeArea
	 * @return
	 * @throws DAOException
	 */
	public Collection<ProgramaVO> findProgramasByUserReport(Long codeUser,
			String userGroupName, Long codeArea) throws DAOException {
		IProgramaDAO programaDAO = null;
		try {
			Connection con = ConexionFactory.getInstance().getConnection();
			programaDAO = DAOFactory.getDAOFactory(DAOFactory.ORACLE)
					.getProgramaDAO(con);
			return programaDAO.findProgramaByUserReport(codeUser,
					userGroupName, codeArea);
		} catch (NamingException e) {
			throw new DAOException(e);
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			if (programaDAO != null) {
				if (!programaDAO.isConnClosed()) {
					programaDAO.close();
				}
			}
		}
	}

	/**
	 * 
	 * @param secretaryCode
	 * @return
	 * @throws DAOException
	 */
	public Collection<ProgramaVO> findProgramaByCriteria(ProgramaVO criteria)
			throws DAOException {
		IProgramaDAO programaDAO = null;
		try {
			Connection con = ConexionFactory.getInstance().getConnection();
			programaDAO = DAOFactory.getDAOFactory(DAOFactory.ORACLE)
					.getProgramaDAO(con);
			return programaDAO.findProgramaByCriteria(criteria);
		} catch (NamingException e) {
			throw new DAOException(e);
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			if (programaDAO != null) {
				if (!programaDAO.isConnClosed()) {
					programaDAO.close();
				}
			}
		}
	}

	public Collection<ProgramaVO> findAllProgramaByCriteria(ProgramaVO criteria)
			throws DAOException {
		IProgramaDAO programaDAO = null;
		try {
			Connection con = ConexionFactory.getInstance().getConnection();
			programaDAO = DAOFactory.getDAOFactory(DAOFactory.ORACLE)
					.getProgramaDAO(con);
			return programaDAO.findAllProgramaByCriteria(criteria);
		} catch (NamingException e) {
			throw new DAOException(e);
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			if (programaDAO != null) {
				if (!programaDAO.isConnClosed()) {
					programaDAO.close();
				}
			}
		}
	}

	/**
	 * 
	 * @param criteria
	 *            bean with all data to filter the query.
	 * @param codeUser
	 *            code of the user at the moment connected
	 * @param groupUserName
	 *            Name of the user group
	 * @param areaUser
	 *            Code of the area to which the user belongs
	 * @notes This method returns all the programs to which a user has
	 *        permission, as long as they are associate to a seat
	 * @author Carlos Ruiz & Daniel Monsalve
	 * @date 04/05/2007
	 * @return Collection with the programs
	 * @throws java.sql.SQLException
	 */
	public Collection<ProgramaVO> findProgramaByCriteria(ProgramaVO criteria,
			Long codeUser, String groupUserName, Long areaUser)
			throws DAOException {
		IProgramaDAO programaDAO = null;
		try {
			Connection con = ConexionFactory.getInstance().getConnection();
			programaDAO = DAOFactory.getDAOFactory(DAOFactory.ORACLE)
					.getProgramaDAO(con);
			return programaDAO.findProgramaByCriteria(criteria, codeUser,
					groupUserName, areaUser);
		} catch (NamingException e) {
			throw new DAOException(e);
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			if (programaDAO != null) {
				if (!programaDAO.isConnClosed()) {
					programaDAO.close();
				}
			}
		}
	}

	public Collection<ProgramaVO> findProgramaByCriteriaOB(ProgramaVO criteria,
			Long codeUser, String groupUserName, Long areaUser)
			throws DAOException {
		IProgramaDAO programaDAO = null;
		try {
			Connection con = ConexionFactory.getInstance().getConnection();
			programaDAO = DAOFactory.getDAOFactory(DAOFactory.ORACLE)
					.getProgramaDAO(con);
			return programaDAO.findProgramaByCriteriaOB(criteria, codeUser,
					groupUserName, areaUser);
		} catch (NamingException e) {
			throw new DAOException(e);
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			if (programaDAO != null) {
				if (!programaDAO.isConnClosed()) {
					programaDAO.close();
				}
			}
		}
	}

	public Collection<ProgramaVO> findProgramaWithoutSede(ProgramaVO criteria,
			Long codeUser, String groupUserName, Long areaUser)
			throws DAOException {
		IProgramaDAO programaDAO = null;
		try {
			Connection con = ConexionFactory.getInstance().getConnection();
			programaDAO = DAOFactory.getDAOFactory(DAOFactory.ORACLE)
					.getProgramaDAO(con);
			return programaDAO.findProgramaWithoutSede(criteria, codeUser,
					groupUserName, areaUser);
		} catch (NamingException e) {
			throw new DAOException(e);
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			if (programaDAO != null) {
				if (!programaDAO.isConnClosed()) {
					programaDAO.close();
				}
			}
		}
	}

	/**
	 * 
	 * @param criteria
	 *            bean with all data to filter the query.
	 * @param groupUserName
	 *            Name of the user group
	 * @param codeUser
	 *            code of the user at the moment connected
	 * @param Code
	 *            of the area to which the user belongs
	 * @notes This method returns all the programs to which a user has
	 *        permission.
	 * @return Collection with the programs
	 * @throws java.sql.SQLException
	 */
	public Collection<ProgramaVO> findProgramaByCriteria(ProgramaVO criteria,
			String groupUserName, Long codeUser, Long areaUser)
			throws DAOException {
		IProgramaDAO programaDAO = null;
		try {
			Connection con = ConexionFactory.getInstance().getConnection();
			programaDAO = DAOFactory.getDAOFactory(DAOFactory.ORACLE)
					.getProgramaDAO(con);
			return programaDAO.findProgramaByCriteria(criteria, groupUserName,
					codeUser, areaUser);
		} catch (NamingException e) {
			throw new DAOException(e);
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			if (programaDAO != null) {
				if (!programaDAO.isConnClosed()) {
					programaDAO.close();
				}
			}
		}
	}

	// Metodo que ejecuta la busqueda de programas para el reporte detallado
	// de beneficios otorgados
	public Collection<ProgramaVO> findProgramaByCriteriaReport(
			ProgramaVO criteria, String groupUserName, Long codeUser,
			Long areaUser) throws DAOException {
		IProgramaDAO programaDAO = null;
		try {
			Connection con = ConexionFactory.getInstance().getConnection();
			programaDAO = DAOFactory.getDAOFactory(DAOFactory.ORACLE)
					.getProgramaDAO(con);
			return programaDAO.findProgramaByCriteriaReport(criteria,
					groupUserName, codeUser, areaUser);
		} catch (NamingException e) {
			throw new DAOException(e);
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			if (programaDAO != null) {
				if (!programaDAO.isConnClosed()) {
					programaDAO.close();
				}
			}
		}
	}

	public Collection<ProgramaVO> findProgramaWithoutVigencia(
			String groupUserName, Long codeUser, Long areaUser)
			throws DAOException {
		IProgramaDAO programaDAO = null;
		try {
			Connection con = ConexionFactory.getInstance().getConnection();
			programaDAO = DAOFactory.getDAOFactory(DAOFactory.ORACLE)
					.getProgramaDAO(con);
			return programaDAO.findProgramaWithoutVigencia(groupUserName,
					codeUser, areaUser);
		} catch (NamingException e) {
			throw new DAOException(e);
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			if (programaDAO != null) {
				if (!programaDAO.isConnClosed()) {
					programaDAO.close();
				}
			}
		}
	}

	/**
	 * Autor DORIS
	 * 
	 * @param codePrograma
	 * @return
	 */
	public boolean EstadoPrograma(ProgramaVO criteria) {
		IProgramaDAO programaDAO = null;
		try {
			Connection con = ConexionFactory.getInstance().getConnection();
			programaDAO = DAOFactory.getDAOFactory(DAOFactory.ORACLE)
					.getProgramaDAO(con);
			return programaDAO.EstadoPrograma(criteria);
		} catch (NamingException e) {
			throw new DAOException(e);
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			if (programaDAO != null) {
				if (!programaDAO.isConnClosed()) {
					programaDAO.close();
				}
			}
		}
	}

	/**
	 * Autor DORIS
	 * 
	 * @param codePrograma
	 * @return
	 */
	public int getActivarProg(ProgramaVO criteria) {
		IProgramaDAO programaDAO = null;
		try {
			Connection con = ConexionFactory.getInstance().getConnection();
			programaDAO = DAOFactory.getDAOFactory(DAOFactory.ORACLE)
					.getProgramaDAO(con);
			return programaDAO.getActivarProg(criteria);
		} catch (NamingException e) {
			throw new DAOException(e);
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			if (programaDAO != null) {
				if (!programaDAO.isConnClosed()) {
					programaDAO.close();
				}
			}
		}
	}

	public Collection<ProgramaVO> getProgramasVigentes(ProgramaVO criteria,
			String groupUserName, Long codeUser, Long areaUser,
			boolean controlAcceso) throws DAOException {
		IProgramaDAO programaDAO = null;
		try {
			Connection con = ConexionFactory.getInstance().getConnection();
			programaDAO = DAOFactory.getDAOFactory(DAOFactory.ORACLE)
					.getProgramaDAO(con);
			return programaDAO.getProgramasVigentes(criteria, groupUserName,
					codeUser, areaUser, controlAcceso);
		} catch (NamingException e) {
			throw new DAOException(e);
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			if (programaDAO != null) {
				if (!programaDAO.isConnClosed()) {
					programaDAO.close();
				}
			}
		}
	}

	public String getNameByCode(String strCodeProg) throws DAOException {
		IProgramaDAO programaDAO = null;
		try {
			Connection con = ConexionFactory.getInstance().getConnection();
			programaDAO = DAOFactory.getDAOFactory(DAOFactory.ORACLE)
					.getProgramaDAO(con);
			return programaDAO.getNameByCode(strCodeProg);
		} catch (NamingException e) {
			throw new DAOException(e);
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			if (programaDAO != null) {
				if (!programaDAO.isConnClosed()) {
					programaDAO.close();
				}
			}
		}
	}

	public Collection<ProgramaVO> findProgramaByCriteriaWithGestor(
			ProgramaVO criteria, String groupUserName, Long codeUser,
			Long areaUser) throws DAOException {
		IProgramaDAO programaDAO = null;
		try {
			Connection con = ConexionFactory.getInstance().getConnection();
			programaDAO = DAOFactory.getDAOFactory(DAOFactory.ORACLE)
					.getProgramaDAO(con);
			return programaDAO.findProgramaByCriteriaWithGestor(criteria,
					groupUserName, codeUser, areaUser);
		} catch (NamingException e) {
			throw new DAOException(e);
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			if (programaDAO != null) {
				if (!programaDAO.isConnClosed()) {
					programaDAO.close();
				}
			}
		}
	}

}
